﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BolsaTrabajo.BusinessEntities;

namespace BolsaTrabajo.DataAccess
{
    public class EmpresaData
    {
        private String stConnection = String.Empty;

        public EmpresaData()
        {
            stConnection = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
        }

        public Empresa CargarEntidad(Int32 id)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_Empresa_ListarPorId";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@id", ParameterDirection.Input, SqlDbType.Int, id);

            Empresa item = new Empresa();
            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    item.IdEmpresa = dr.GetInt32(dr.GetOrdinal("IdEmpresa"));
                    item.RUC = dr.GetString(dr.GetOrdinal("RUC"));
                    item.RazonSocial = dr.GetString(dr.GetOrdinal("RazonSocial"));
                    //item.Correo = dr.GetString(dr.GetOrdinal("Correo"));
                    //item.Password = dr.GetString(dr.GetOrdinal("Password"));
                    item.PersonaContacto = dr.GetString(dr.GetOrdinal("PersonaContacto"));
                    item.Distrito = dr.GetString(dr.GetOrdinal("Distrito"));
                    item.Direccion = dr.GetString(dr.GetOrdinal("Direccion"));
                    item.CorreoElectronico = dr.GetString(dr.GetOrdinal("CorreoElectronico"));
                    item.Telefono = dr.GetString(dr.GetOrdinal("Telefono"));
                    item.PaginaWeb = dr.GetString(dr.GetOrdinal("PaginaWeb"));
                    item.IdCategoria = dr.GetInt32(dr.GetOrdinal("IdCategoria"));
                    item.Descripcion = dr.GetString(dr.GetOrdinal("Descripcion"));
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return item;
        }

        public bool Registrar(Empresa item)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_Empresa_Registrar";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@applicationID", ParameterDirection.Input, SqlDbType.UniqueIdentifier, item.ApplicationId);
            SQLHelper.AddParam(ref cmd, "@userID", ParameterDirection.Input, SqlDbType.UniqueIdentifier, item.UserId);
            SQLHelper.AddParam(ref cmd, "@ruc", ParameterDirection.Input, SqlDbType.VarChar, 50, item.RUC);
            SQLHelper.AddParam(ref cmd, "@RazonSocial", ParameterDirection.Input, SqlDbType.VarChar, 50, item.RazonSocial);
            //SQLHelper.AddParam(ref cmd, "@Correo", ParameterDirection.Input, SqlDbType.VarChar, item.Correo);
            //SQLHelper.AddParam(ref cmd, "@Password", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Password);
            SQLHelper.AddParam(ref cmd, "@PersonaContacto", ParameterDirection.Input, SqlDbType.VarChar, 50, item.PersonaContacto);
            SQLHelper.AddParam(ref cmd, "@Distrito", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Distrito);
            SQLHelper.AddParam(ref cmd, "@Direccion", ParameterDirection.Input, SqlDbType.VarChar, item.Direccion);
            SQLHelper.AddParam(ref cmd, "@CorreoElectronico", ParameterDirection.Input, SqlDbType.VarChar, 50, item.CorreoElectronico);
            SQLHelper.AddParam(ref cmd, "@Telefono", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Telefono);
            SQLHelper.AddParam(ref cmd, "@PaginaWeb", ParameterDirection.Input, SqlDbType.VarChar, item.PaginaWeb);
            SQLHelper.AddParam(ref cmd, "@IdCategoria", ParameterDirection.Input, SqlDbType.Int, item.IdCategoria);
            SQLHelper.AddParam(ref cmd, "@Descripcion", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Descripcion);

            bool resultado = false;

            try
            {
                //if (cn.State == ConnectionState.Closed)
                //{
                //    cn.Open();
                //}

                cn.Open();
                cmd.ExecuteNonQuery();
                resultado = true;
            }
            catch (Exception)
            {
                throw new Exception("Error de Registro");
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
                cn.Dispose();
            }
            return resultado;
        }

        public bool Actualizar(Empresa item)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_Empresa_Actualizar";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@IdEmpresa", ParameterDirection.Input, SqlDbType.Int, item.IdEmpresa);
            SQLHelper.AddParam(ref cmd, "@RUC", ParameterDirection.Input, SqlDbType.VarChar, 50, item.RUC);
            SQLHelper.AddParam(ref cmd, "@RazonSocial", ParameterDirection.Input, SqlDbType.VarChar, 50, item.RazonSocial);
            SQLHelper.AddParam(ref cmd, "@Correo", ParameterDirection.Input, SqlDbType.VarChar, item.Correo);
            SQLHelper.AddParam(ref cmd, "@Password", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Password);
            SQLHelper.AddParam(ref cmd, "@PersonaContacto", ParameterDirection.Input, SqlDbType.VarChar, 50, item.PersonaContacto);
            SQLHelper.AddParam(ref cmd, "@Distrito", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Distrito);
            SQLHelper.AddParam(ref cmd, "@Direccion", ParameterDirection.Input, SqlDbType.VarChar, item.Direccion);
            SQLHelper.AddParam(ref cmd, "@CorreoElectronico", ParameterDirection.Input, SqlDbType.VarChar, 50, item.CorreoElectronico);
            SQLHelper.AddParam(ref cmd, "@Telefono", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Telefono);
            SQLHelper.AddParam(ref cmd, "@PaginaWeb", ParameterDirection.Input, SqlDbType.VarChar, item.PaginaWeb);
            SQLHelper.AddParam(ref cmd, "@IdCategoria", ParameterDirection.Input, SqlDbType.Int, 50, item.IdCategoria);
            SQLHelper.AddParam(ref cmd, "@Descripcion", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Descripcion);

            bool resultado = false;

            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                resultado = true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
                cn.Dispose();
            }
            return resultado;
        }

        public bool Eliminar(Empresa item)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_Empresa_Eliminar";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@IdEmpresa", ParameterDirection.Input, SqlDbType.Int, item.IdEmpresa);

            bool resultado = false;

            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                resultado = true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
                cn.Dispose();
            }
            return resultado;
        }

        public DataTable ListarEmpresas(String razonsocial, Int32 estado)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmdSql = new SqlCommand();
            cmdSql.CommandTimeout = 216000000;
            DataTable dtListaEmpresa = new DataTable();
            SqlDataAdapter adpSol = new SqlDataAdapter();

            try
            {
                cmdSql.Connection = cn;
                {
                    adpSol.SelectCommand = new System.Data.SqlClient.SqlCommand();
                    adpSol.SelectCommand.Connection = cn;
                    adpSol.SelectCommand.CommandText = "usp_Empresa_ListarPorRazonSocialEstado";
                    adpSol.SelectCommand.CommandType = CommandType.StoredProcedure;
                    adpSol.SelectCommand.Parameters.Add(new SqlParameter("@razonsocial", SqlDbType.VarChar, 50)).Value = razonsocial;
                    adpSol.SelectCommand.Parameters.Add(new SqlParameter("@estado", SqlDbType.Int)).Value = estado;
                }
                adpSol.Fill(dtListaEmpresa);
                dtListaEmpresa.TableName = "ListaEmpresa";
                return dtListaEmpresa;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
                cmdSql.Dispose();
            }
        }

        public Empresa ObtenerEntidadPorRUC(String ruc)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_Empresa_ListarPorRuc";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@ruc", ParameterDirection.Input, SqlDbType.VarChar, 50, ruc);

            Empresa item = new Empresa();
            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    item.IdEmpresa = dr.GetInt32(dr.GetOrdinal("IdEmpresa"));
                    item.RUC = dr.GetString(dr.GetOrdinal("RUC"));
                    item.RazonSocial = dr.GetString(dr.GetOrdinal("RazonSocial"));
                    item.Correo = dr.GetString(dr.GetOrdinal("Correo"));
                    item.Password = dr.GetString(dr.GetOrdinal("Password"));
                    item.PersonaContacto = dr.GetString(dr.GetOrdinal("PersonaContacto"));
                    item.Distrito = dr.GetString(dr.GetOrdinal("Distrito"));
                    item.Direccion = dr.GetString(dr.GetOrdinal("Direccion"));
                    item.CorreoElectronico = dr.GetString(dr.GetOrdinal("CorreoElectronico"));
                    item.Telefono = dr.GetString(dr.GetOrdinal("Telefono"));
                    item.PaginaWeb = dr.GetString(dr.GetOrdinal("PaginaWeb"));
                    item.IdCategoria = dr.GetInt32(dr.GetOrdinal("IdCategoria"));
                    item.Descripcion = dr.GetString(dr.GetOrdinal("Descripcion"));
                }
                dr.Close();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return item;
        }
    }
}
